package b_poi;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class PoiOutPutExcel {
	public static void main(String[] args) throws Exception {
		String[] headerValues=new String[]{"姓名","年龄","生日","家乡"};
		String[] cellValues1=new String[]{"熊丹丹","20","1997-11-16","达州"};
		String[] cellValues2=new String[]{"袁满","28","1990-12-25","平谷"};
		List<String[]> objList=new ArrayList<>();
		objList.add(cellValues1);
		objList.add(cellValues2);
		HSSFWorkbook workBook = new HSSFWorkbook();
		//创建一个sheet
		HSSFSheet sheet = workBook.createSheet();
		//不显示网格线
		sheet.setDisplayGridlines(false);
		//设置列宽,设置表格默认列宽为15字节
//		sheet.setDefaultColumnWidth(15);
		/************** 生成单元格样式 ****************/
		CellStyle styleOdd=creatStyle(workBook,1);
		CellStyle styleEven = creatStyle(workBook,2);
		
		//生成文件内容(后面生成表头)
		for (int i = 0; i < objList.size(); i++) {
			String[] cellValues=objList.get(i);
			//从第二行开始写入
			HSSFRow row = sheet.createRow(i+1);
			
			//奇,偶行样式不一样
			CellStyle style;
			if ((row.getRowNum() & 1) == 1) {
				style=styleOdd;
			} else {
				style=styleEven;
			}
			
			for (int j = 0; j < cellValues.length; j++) {
				setCell(row,j,style,cellValues[j]);
			}
		}
		//最后生成表头文件,并调整列宽
		CellStyle headerStyle = creatStyle(workBook, 0);
		HSSFRow headerRow = sheet.createRow(0);
		for (int j = 0; j < headerValues.length; j++) {
			setCell(headerRow, j, headerStyle, headerValues[j]);
			sheet.autoSizeColumn(j,true);
		}
		
		/**********************输出到文件***************************/
		Scanner input=new Scanner(System.in);
		System.out.println("请输入文件名:");
		String xlsName=input.next();
		File file =new File("D://"+xlsName+".xls");
		workBook.write(file);
		System.out.println("写入文件成功!");
		workBook.close();
		
		/***********************返回字节数组**************************/
//		try(ByteArrayOutputStream bos = new ByteArrayOutputStream()){
//			workBook.write(bos);
//			byte[] bytes=bos.toByteArray();
//			workBook.close();
//		}
		
	}
	
	
	
	private static void setCell(HSSFRow row, int column, CellStyle style,
			String cellValue) {
		HSSFCell cell = row.createCell(column);
		cell.setCellStyle(style);
		if (StringUtils.isBlank(cellValue)) {
			cellValue="";
		}
		//去掉两端空格
		cellValue = StringUtils.trim(cellValue);
		
		cell.setCellValue(cellValue);
	}



	private static  CellStyle creatStyle(HSSFWorkbook workBook, int type) {
		HSSFCellStyle style = workBook.createCellStyle();
		//上边框
		style.setBorderTop(BorderStyle.THIN);
		style.setTopBorderColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
		//右边框
		style.setBorderTop(BorderStyle.THIN);
		style.setTopBorderColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
		//下边框
		style.setBorderTop(BorderStyle.THIN);
		style.setTopBorderColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
		//左边框
		style.setBorderTop(BorderStyle.THIN);
		style.setTopBorderColor(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
		
		//水平居中,自适应(HSSFCellStyle.ALIGN_CENTER)
		style.setAlignment(HorizontalAlignment.JUSTIFY);
		//垂直居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		//设置自动换行
		style.setWrapText(true);
		
		//字体
		HSSFFont font = workBook.createFont();
		//字体大小
		font.setFontHeightInPoints((short)11);
		//设置字体
		font.setFontName("宋体");
		font.setCharSet(HSSFFont.DEFAULT_CHARSET);
		style.setFont(font);
		
		//设置格式
		HSSFDataFormat format = workBook.createDataFormat();
		style.setDataFormat(format.getFormat("@"));//文本格式
		
		if (type == 0) {//表头样式
			//设置背景色
			style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
			//设置单元格填充的样式
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			//水平居中(HSSFCellStyle.ALIGN_CENTER)
			style.setAlignment(HorizontalAlignment.CENTER);
			//字体
			font.setFontHeightInPoints((short)11);
			font.setColor(IndexedColors.WHITE.getIndex());
			font.setBold(true);//加粗
		} else if(type == 2) {
			style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());
			//设置单元格填充的样式
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		}
		return style;
	}
	
	
	
}
